package com.bawan.vims.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.bawan.vims.common.IDGenerator;

/**
 * 利用POI库
 * <p> Description	: Excel 操作工具类型 </p>
 * <p> Copyright	: Copyright (c) 2015 by FITT</p>
 * <p> Author 		: ZCC </p>
 * <p> Create		: 2015年3月20日 上午11:29:27 </p> 
 * <p> version 		: 1.0 </p>
 */
public class ExcelHelper {
	private static Logger logger = LoggerFactory.getLogger(ExcelHelper.class);
	
	/**
	 * 解析excel
	 */
	public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {
		
		Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();
		
		InputStream in = null;
		Workbook wb = null;
		try {
			File excelFile = new File(excelFilePath);
			if (excelFile == null || !excelFile.exists()) {
				logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
				return null;
			}
			in = new FileInputStream(excelFile);
			
			String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
			if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
				logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
				return null;
			} /*else if ("xls".equals(suffix)){
				wb = new HSSFWorkbook(in);
			} else if("xlsx".equals(suffix)) {
				wb = new XSSFWorkbook(in);
			}*/
			
			wb = WorkbookFactory.create(in); // POI 3.8之后新增的方法, 里面已经实现针对xls、xlsx格式文件的
			int sheetSize = 0;
			
			while(true) {
				Sheet sheet = wb.getSheetAt(sheetSize);
				if (sheet == null) {
					break;
				}
				String sheetName = sheet.getSheetName();

				List<Map<String, Object>> sheetContent = new ArrayList<Map<String,Object>>();
				for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum ++) {
					Row row = sheet.getRow(rowNum);
					Map<String, Object> rowMap = new HashMap<String, Object>();
					StringBuffer rowContent = new StringBuffer("insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
					rowContent.append("'").append(IDGenerator.getID(32)).append("',");
					for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
						Cell cell = row.getCell(cellIndex);
//						if (cell == null) {
//							rowMap.put(rowNum + "_" + cellIndex, null);
//						} else {
//							rowMap.put(rowNum + "_" + cellIndex, cell.toString());
//						}
							if (cellIndex == 2) {
								if (cell == null) {
									rowContent.append(0).append(",");
								} else if ("mpv".equalsIgnoreCase(cell.toString())) {
									rowContent.append(1).append(",");
								} else if ("suv".equalsIgnoreCase(cell.toString())) {
									rowContent.append(2).append(",");
								} else if ("紧凑型车".equalsIgnoreCase(cell.toString())) {
									rowContent.append(3).append(",");
								} else if ("微型车".equalsIgnoreCase(cell.toString())) {
									rowContent.append(4).append(",");
								} else if ("小型车".equalsIgnoreCase(cell.toString())) {
									rowContent.append(5).append(",");
								} else if ("中型车".equalsIgnoreCase(cell.toString())) {
									rowContent.append(6).append(",");
								}
								continue;
							}
						
							if (cell == null || cell.toString().trim().length() == 0) {
								if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3 || cellIndex == 6) {
									rowContent.append("default").append(",");
								} else {
									rowContent.append("0").append(",");
								}
							} else {
								if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3 || cellIndex == 6) {
									rowContent.append("'").append(cell.toString()).append("',");
								} else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8 || cellIndex == 9) {
									String value = cell.toString().substring(0, cell.toString().indexOf("."));
									rowContent.append(Integer.valueOf(value)).append(",");
								} else {
									rowContent.append(cell.toString()).append(",");
								}
							}
					}
					String sql = rowContent.toString();
					sql = sql.substring(0, sql.length() - 1);
					sql += ");";
					System.out.println(sql);
					sheetContent.add(rowMap);
				}
				
				result.put(sheetName, sheetContent);
				sheetSize ++;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
		} finally {
			try {
				if (wb != null) {
					wb.close();
					wb = null;
				}
			} catch (IOException e1) {}
			
			try {
				if (in != null) {
					in.close();
					in = null;
				}
			} catch (IOException e) {}
		}
		
		return result;
	}
	
	/**
	 * 导出excel格式数据
	 */
	public static void exportExcel() {
		
	}
	
	public static void main(String[] args) {
		String excelFilePath = "E:\\lean-soft-suntek\\database-bak\\汽车报表需求\\201502\\需求\\20141216-v2.xlsx";
		Map<String, List<Map<String, Object>>> excel = ExcelHelper.parserExcel(excelFilePath);
		for (Iterator ite = excel.keySet().iterator(); ite.hasNext();) {
			String sheetName = (String)ite.next();
			List<Map<String, Object>> sheetContent = excel.get(sheetName);
			StringBuffer rowContent = new StringBuffer("insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
			for (Map<String, Object> row : sheetContent) {
				for (Iterator it = row.keySet().iterator(); it.hasNext();) {
					String key = (String)it.next();
					Object value = row.get(key);
					if (value == null || value.toString().trim().length() == 0) {
						rowContent.append("default").append(",");
					} else {
						rowContent.append(value.toString()).append(",");
					}
					String sql = rowContent.toString();
					sql = sql.substring(0, sql.length());
					sql += ") \n go \n";
					System.out.println(sql);
				}
			}
		}
	}
}
